package com.crm.dao.before_sale;

import com.crm.pojo.*;
import com.crm.vo.CompetitorVO;
import com.crm.vo.OfferDetailsVO;
import com.crm.vo.PlanDetailsVO;
import com.crm.vo.SalePlanDetailsVO;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.math.BigDecimal;
import java.util.List;

public interface CompetitorDAO extends CrudRepository<Competitor, Long>, JpaSpecificationExecutor<Competitor> {
    //获取竞争对手总数量
    @Query("select count(c) from Competitor  c where c.competitorIsDel='否'")
    public Long getCompetitorExistCount();
    //分页查询竞争对手
    @Query("select new com.crm.vo.CompetitorVO(competitor,salechance.chanceTheme,clientinfo.cname)" +
            "from Competitor competitor,ClientInfo clientinfo,SaleChance salechance where clientinfo.cid=competitor.clientId and salechance.chId=competitor.chanceId and competitor.competitorIsDel='否' order by competitor.comId desc")
    public List<CompetitorVO> queryCompetitorsByPage(Pageable pageable);

    //根据id删除竞争对手
    @Query(nativeQuery = true, value = "update COMPETITOR set COMPETITOR_IS_DEL='是' where COM_ID=?1")
    @Modifying
    public void deleteCompetitorById(Long comId);

    //根据id查询销售机会主题
    @Query("select s.chanceTheme from SaleChance s where s.saleChanceIsDel='否'and s.chId=?1")
    public List<String> findAllSaleChanceNameByChId(Long chId);

    //获取客户
    @Query(" from ClientInfo c where c.clientInfoIsDel='否'")
    public List<ClientInfo> findAllClientInfoName();

    //获取销售机会
    @Query("from SaleChance s where s.saleChanceIsDel='否'")
    public List<SaleChance> findAllSaleChanceName();
    //根据竞争对手名称获取一个竞争对手（用于添加时判断该对手是否已存在）
    @Query("from Competitor c where c.competitorIsDel='否' and c.comName=?1")
    public List<Competitor> queryCompetitorsByComName(String comName);



    //以下是客户视图需要的接口

    /**
     * 根据客户编号查询销售机会
     * @param clientId
     * @return
     */
    @Query("select saleChance from SaleChance saleChance where saleChance.clientId=?1 and saleChance.saleChanceIsDel='否'")
    public List<SaleChance> getSaleChnceListByClientId(BigDecimal clientId);
    /**
     * 根据客户编号查询客户需求
     * @param clientId
     * @return
     */
    @Query("select clientNeed from ClientNeed clientNeed where clientNeed.clientId=?1 and clientNeed.clientNeedIsDel='否'")
    public List<ClientNeed> getClientNeedListByClientId(BigDecimal clientId);
    /**
     * 根据客户编号查询解决方案
     * @param clientId
     * @return
     */
    @Query("select scheme from Scheme scheme where scheme.clientId=?1 and scheme.schemeIsDel='否'")
    public List<Scheme> getSchemeListByClientId(Long clientId);
    /**
     * 根据客户编号查询竞争对手
     * @param clientId
     * @return
     */
    @Query("select competitor from Competitor competitor where competitor.clientId=?1 and competitor.competitorIsDel='否'")
    public List<Competitor> getCompetitorListByClientId(BigDecimal clientId);

    /**
     * 根据客户编号查询销售计划及计划明细
     * @param clientId
     * @return
     */
    @Query("select new com.crm.vo.PlanDetailsVO(pl,pr)from SalePlan salePlan,PlanDetail pl,ProductionDetail pr" +
            " where salePlan.goalClientId=?1 and pr.pid=pl.salePlanId and pl.salePlan=salePlan and pl.planDetailIsDel='否'")//salePlanId为产品id
    public List<PlanDetailsVO> getSalePlanDetailsVOListByClientId(BigDecimal clientId);

    @Query("select new com.crm.vo.OfferDetailsVO(od,pr) from Offer offer,OfferDetail od,ProductionDetail pr where offer.clientId=?1 and pr.pid=od.pId and od.offer=offer and od.offerDetailIsDel='否'")
    public List<OfferDetailsVO> getOfferDetailVOListByClientId(BigDecimal clientId);





}
